import json
import pandas as pd
import requests
from concurrent.futures import ThreadPoolExecutor, as_completed
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows



def get_ip_info(ip):
    key = "xxxxxx"
    url = f"https://restapi.amap.com/v3/ip?key={key}&ip={ip}"
    response = requests.get(url)
    if response.status_code == 200:
        return response.json().get("province")
    else:
        return None


# 定义一个函数来处理每一行日志
def process_log_line(line):
    try:
        log_entry = json.loads(line)
        ip_values = log_entry["ip"].split(", ")
        ip_value = ip_values[0]
        request_value = log_entry["request"]
        return {"ip": ip_value, "request": request_value, "http_user_agent": log_entry["http_user_agent"]}
    except json.JSONDecodeError:
        print(f"Failed to parse line: {line}")
        return None

# 初始化 DataFrame
df = pd.DataFrame(columns=["ip", "request", "http_user_agent"])

with open(r'/xxx/short_2024-07-30.log', 'r', encoding='utf-8') as log_file:
    with ThreadPoolExecutor(max_workers=30) as executor:
        futures = []
        results = []
        for line in log_file:
            # 使用多线程来处理每一行日志，将所有的Future 对象存储在futures 列表中
            futures.append(executor.submit(process_log_line, line))
        # 收集所有线程的结果，将结果存储在results 列表中
        for future in as_completed(futures):
            result = future.result()
            if result is not None:
                results.append(result)

df = pd.DataFrame(results)

# 按 IP 排序
df_sorted = df.sort_values(by="ip")
df_sorted.to_excel('ipsorted.xlsx', index=False)



input_file = 'ipsorted.xlsx'
df = pd.read_excel(input_file, sheet_name='Sheet1')

# 按 IP 汇总
ip_summary = df['ip'].value_counts().reset_index()
ip_summary.columns = ['ip', 'count']

# 按 request 汇总
request_summary = df['request'].value_counts().reset_index()
request_summary.columns = ['request', 'count']

# 按 user agent 汇总
user_agent_summary = df['http_user_agent'].value_counts().reset_index()
user_agent_summary.columns = ['http_user_agent', 'count']

# 创建一个工作簿并添加工作表
wb = Workbook()
ws1 = wb.active
ws1.title = "IP Summary"
ws2 = wb.create_sheet(title="Request Summary")
ws3 = wb.create_sheet(title="User Agent Summary")

# 限制调用 get_ip_location 的次数
call_limit = 50
call_count = 0
# 将汇总结果写入工作表
for r in dataframe_to_rows(ip_summary, index=False, header=True):
    ip = r[0]
    r = list(r)
    if call_count < call_limit:
        location = get_ip_info(ip)
        call_count += 1
    else:
        location = ""  # 其他行不调用 get_ip_location
    r.insert(2, location)  # 插入地理位置到第三列

    # 检查每行的数据，主要是三个字段，ip,request, user_agent 字段并转换所有值为字符串，确保它们可以写入 Excel
    for i in range(len(r)):
        if not isinstance(r[i], (int, float, str)):
            r[i] = str(r[i])

    ws1.append(r)
for r in dataframe_to_rows(request_summary, index=False, header=True):
    ws2.append(r)
for r in dataframe_to_rows(user_agent_summary, index=False, header=True):
    ws3.append(r)

# 保存工作簿
output_file = 'summarized_data.xlsx'
wb.save(output_file)


